<?php

declare(strict_types=1);

namespace App\CustHub\Mapper;

use App\CustHub\Model\ChAddFriendGroup;
use Hyperf\DbConnection\Db;
use Mine\Abstracts\AbstractMapper;

class ActiveAddFriendGroupMapper extends AbstractMapper
{
    public function assignModel()
    {
        $this->model = ChAddFriendGroup::class;
    }

    public function groupData($params)
    {
        $uid = $params['uid'];
        $timeRange = $params['time'] ?? null;
        $startDate = null;
        $endDate = null;

        if ($timeRange) {
            $dates = explode(',', $timeRange);
            $startDate = trim($dates[0]) . ' 00:00:00';
            $endDate = isset($dates[1]) ? trim($dates[1]) . ' 23:59:59' : $startDate;
        }

        // 构建子查询，处理时间过滤
        $subQuery = Db::table('ch_add_friend as b');
        if ($timeRange) {
            $subQuery->whereBetween('b.add_time', [$startDate, $endDate]);
        }
        $subQuery->select('b.group_id', 'b.id', 'b.wechat_status');

        $query = Db::table('ch_add_friend_group as a')
            ->leftJoinSub($subQuery, 'b', 'a.id', '=', 'b.group_id')
            ->where('a.uid', $uid)
            ->select(
                'a.id',
                'a.group_name',
                Db::raw('IFNULL(COUNT(b.id), 0) AS total_greetings'),
                Db::raw('IFNULL(SUM(b.wechat_status = 2), 0) AS passed_friends'),
                Db::raw('IFNULL(ROUND(IFNULL(SUM(b.wechat_status = 2), 0) / NULLIF(IFNULL(COUNT(b.id), 0), 0) * 100, 2), 0) AS pass_rate'),
                Db::raw('IFNULL(SUM(b.wechat_status = 0), 0) AS not_found_wechat')
            )
            ->groupBy('a.id')
            ->orderBy('a.id');
        return $query;
    }
}